/**
 *Copyright (c) 2024 watereyes
 * safetymanagement is licensed under Mulan PubL v2.
 * You can use this software according to the terms and conditions of the Mulan
 * PubL v2.
 * You may obtain a copy of Mulan PubL v2 at:
 *          http://license.coscl.org.cn/MulanPubL-2.0
 * THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
 * EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
 * MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
 * See the Mulan PubL v2 for more details.
 */
package com.koron.report.mapper.statistics;

import com.koron.report.bean.TrainingStatistic;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

public interface TrainingStatisticMapper {

    /**
     * 查询参加三级安全教育培训考试不合格的员工（统计层级为员工）
     * @param startDate 统计开始时间
     * @param endDate 统计结束时间
     * @return
     */
    @Select("select tr.staff_code AS objectId ,tr.staff_name AS objectName , count(tr.id) AS kpiValue " +
            "from training_record tr LEFT JOIN training_plan tp ON tr.training_plan_id = tp.id where tr.training_time " +
            "between #{startDate} and #{endDate} and tr.training_type = '1174583072844075008' and tp.state='0'  " +
            "and (tr.is_pass IS NULL OR tr.is_pass='N') group by tr.staff_code, tr.staff_name")
    List<TrainingStatistic> queryUnqualified(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

    /**
     * 查询三级安全教育培训考试合格的数量（统计层级为公司）
     * @param startDate 统计开始时间
     * @param endDate 统计结束时间
     * @return
     */
    @Select("select tr.corp_code AS objectId ,count(tr.id) AS kpiValue " +
            "from training_record tr LEFT JOIN training_plan tp ON tr.training_plan_id = tp.id where tr.training_time " +
            "between #{startDate} and #{endDate} and tr.training_type = '1174583072844075008' " +
            "and tp.state='0' and tr.is_pass = 'Y' group by tr.corp_code")
    List<TrainingStatistic> queryQualified(@Param("startDate")Date startDate, @Param("endDate")Date endDate);

    /**
     * 查询参加三级安全教育培训考试的总数量（统计层级为公司）
     * @param startDate 统计开始时间
     * @param endDate 统计结束时间
     * @return
     */
    @Select("select tr.corp_code AS objectId ,count(tr.id) AS kpiValue " +
            "from training_record tr LEFT JOIN training_plan tp ON tr.training_plan_id = tp.id where tr.training_time " +
            "between #{startDate} and #{endDate} and tr.training_type = '1174583072844075008' and tp.state='0' group by tr.corp_code")
    List<TrainingStatistic> queryTotal(@Param("startDate")Date startDate, @Param("endDate")Date endDate);

    /**
     * 查询三级安全教育培训考试合格的数量（统计层级为集团）
     * @param startDate 统计开始时间
     * @param endDate 统计结束时间
     * @return
     */
    @Select("select count(tr.id) AS kpiValue " +
            "from training_record tr LEFT JOIN training_plan tp ON tr.training_plan_id = tp.id where tr.training_time " +
            "between #{startDate} and #{endDate} and tr.training_type = '1174583072844075008' and tr.is_pass = 'Y'AND tp.state='0'")
    List<TrainingStatistic> queryAllQualified(@Param("startDate")Date startDate, @Param("endDate")Date endDate);

    /**
     * 查询参加三级安全教育培训考试的总数量（统计层级为集团）
     * @param startDate 统计开始时间
     * @param endDate 统计结束时间
     * @return
     */
    @Select("select count(tr.id) AS kpiValue " +
            "from training_record tr LEFT JOIN training_plan tp ON tr.training_plan_id = tp.id where tr.training_time " +
            "between #{startDate} and #{endDate} and tr.training_type = '1174583072844075008'AND tp.state='0'")
    List<TrainingStatistic> queryAllTotal(@Param("startDate")Date startDate, @Param("endDate")Date endDate);

    /**
     * 员工培训课时总数（统计层级为员工）
     * @param startDate 统计开始时间
     * @param endDate 统计结束时间
     * @return
     */
    @Select("select tr.staff_code AS objectId ,tr.staff_name AS objectName , ifnull(sum(tr.attend_hour),0)AS kpiValue " +
            "from training_record tr LEFT JOIN training_plan tp ON tr.training_plan_id = tp.id where tr.training_time " +
            "between #{startDate} and #{endDate} and tp.state='0' group by tr.staff_code, tr.staff_name")
    List<TrainingStatistic> queryTrainingDuration(@Param("startDate")Date startDate, @Param("endDate")Date endDate);

    /**
     * 已制定的年度培训计划（统计层级为公司）
     * @param startDate 统计开始时间
     * @param endDate 统计结束时间
     * @return
     */
    @Select("select tp.corp_code AS objectId ,count(tp.id) AS kpiValue " +
            "from training_plan tp where tp.add_time " +
            "between #{startDate} and #{endDate} and tp.state = '0' group by tp.corp_code")
    List<TrainingStatistic> queryAnnualTrainingPlan(@Param("startDate")Date startDate, @Param("endDate")Date endDate);

    /**
     * 已完成的培训总数（统计层级为公司）
     * @param startDate 统计开始时间
     * @param endDate 统计结束时间
     * @return
     */
    @Select("select tsr.company_code AS objectId,tsr.company_name AS objectName, COUNT(tp.id) AS kpiValue " +
            "FROM training_record tr LEFT JOIN training_plan tp ON tr.training_plan_id = tp.id LEFT JOIN "+
            "training_staff_relation tsr ON tsr.training_plan_id = tp.id where tr.training_time " +
            "between #{startDate} and #{endDate} and tr.grade_status_code = 'finished' AND tp.state = '0' group by tsr.company_code,tsr.company_name")
    List<TrainingStatistic> queryTrainingPlanCompletionByCompany(@Param("startDate")Date startDate, @Param("endDate")Date endDate);

    /**
     * 计划完成培训总数（统计层级为公司）
     * @param startDate 统计开始时间
     * @param endDate 统计结束时间
     * @return
     */
    @Select("select tsr.company_code AS objectId,tsr.company_name AS objectName, COUNT(tp.id) AS kpiValue " +
            "FROM training_record tr LEFT JOIN training_plan tp ON tr.training_plan_id = tp.id LEFT JOIN "+
            "training_staff_relation tsr ON tsr.training_plan_id = tp.id where tr.training_time " +
            "between #{startDate} and #{endDate} AND tp.state = '0' group by tsr.company_code,tsr.company_name")
    List<TrainingStatistic> queryTrainingPlanTotalByCompany(@Param("startDate")Date startDate, @Param("endDate")Date endDate);

    /**
     * 已完成的培训总数（统计层级为集团）
     * @param startDate 统计开始时间
     * @param endDate 统计结束时间
     * @return
     */
    @Select("select COUNT(1) FROM training_record tr LEFT JOIN training_plan tp ON tr.training_plan_id = tp.id " +
            "LEFT JOIN training_staff_relation tsr ON tsr.training_plan_id = tp.id where tr.training_time " +
            "between #{startDate} and #{endDate} and tr.grade_status_code = 'finished' and tp.state = '0' ")
    Integer queryTrainingPlanCompletionByClique(@Param("startDate")Date startDate, @Param("endDate")Date endDate);

    /**
     * 计划完成培训总数（统计层级为集团）
     * @param startDate 统计开始时间
     * @param endDate 统计结束时间
     * @return
     */
    @Select("select COUNT(1) FROM training_record tr LEFT JOIN training_plan tp ON tr.training_plan_id = tp.id " +
            "LEFT JOIN training_staff_relation tsr ON tsr.training_plan_id = tp.id where tr.training_time " +
            "between #{startDate} and #{endDate} and tp.state = '0' ")
    Integer queryTrainingPlanTotalByClique(@Param("startDate")Date startDate, @Param("endDate")Date endDate);

    /**
     * 已完成的培训总数（统计层级为部门）
     * @param startDate 统计开始时间
     * @param endDate 统计结束时间
     * @return
     */
    @Select("select tsr.dept_code AS objectId,tsr.dept_name AS objectName, COUNT(tp.id) AS kpiValue " +
            "FROM training_record tr LEFT JOIN training_plan tp ON tr.training_plan_id = tp.id LEFT JOIN "+
            "training_staff_relation tsr ON tsr.training_plan_id = tp.id where tr.training_time " +
            "between #{startDate} and #{endDate} and tr.grade_status_code = 'finished' AND tp.state = '0' group by tsr.dept_code,tsr.dept_name")
    List<TrainingStatistic> queryTrainingPlanCompletionByDept(@Param("startDate")Date startDate, @Param("endDate")Date endDate);


    /**
     * 计划完成的培训总数（统计层级为部门）
     * @param startDate 统计开始时间
     * @param endDate 统计结束时间
     * @return
     */
    @Select("select tsr.dept_code AS objectId,tsr.dept_name AS objectName, COUNT(tp.id) AS kpiValue " +
            "FROM training_record tr LEFT JOIN training_plan tp ON tr.training_plan_id = tp.id LEFT JOIN "+
            "training_staff_relation tsr ON tsr.training_plan_id = tp.id where tr.training_time " +
            "between #{startDate} and #{endDate} and  tp.state = '0' group by tsr.dept_code,tsr.dept_name")
    List<TrainingStatistic> queryTrainingPlanTotalByDept(@Param("startDate")Date startDate, @Param("endDate")Date endDate);
}
